/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     2010/10/22 21:31:19                          */
/*==============================================================*/


if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.Attachment')
            and   type = 'U')
   drop table dbo.Attachment
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.CarBrand')
            and   type = 'U')
   drop table dbo.CarBrand
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.CarModel')
            and   type = 'U')
   drop table dbo.CarModel
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.Category')
            and   type = 'U')
   drop table dbo.Category
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.Color')
            and   type = 'U')
   drop table dbo.Color
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.Inventory')
            and   type = 'U')
   drop table dbo.Inventory
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Modules')
            and   type = 'U')
   drop table Modules
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.News')
            and   type = 'U')
   drop table dbo.News
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.NewsCategory')
            and   type = 'U')
   drop table dbo.NewsCategory
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.OrderProducts')
            and   type = 'U')
   drop table dbo.OrderProducts
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.PartBrand')
            and   type = 'U')
   drop table dbo.PartBrand
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.ProductCarModelRel')
            and   type = 'U')
   drop table dbo.ProductCarModelRel
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.ProductColorRel')
            and   type = 'U')
   drop table dbo.ProductColorRel
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.ProductFavorablePrice')
            and   type = 'U')
   drop table dbo.ProductFavorablePrice
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.ProductPrice')
            and   type = 'U')
   drop table dbo.ProductPrice
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.Products')
            and   type = 'U')
   drop table dbo.Products
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Purchasers')
            and   type = 'U')
   drop table Purchasers
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Roles')
            and   type = 'U')
   drop table Roles
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.SellOrder')
            and   type = 'U')
   drop table dbo.SellOrder
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.ShipmentAddress')
            and   type = 'U')
   drop table dbo.ShipmentAddress
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Suppliers')
            and   type = 'U')
   drop table Suppliers
go

if exists (select 1
            from  sysobjects
           where  id = object_id('dbo.Users')
            and   type = 'U')
   drop table dbo.Users
go

execute sp_revokedbaccess dbo
go

/*==============================================================*/
/* User: dbo                                                    */
/*==============================================================*/
execute sp_grantdbaccess dbo
go

/*==============================================================*/
/* Table: Attachment                                            */
/*==============================================================*/
create table dbo.Attachment (
   ID                   int                  identity(1,1),
   ObjectID             bigint               null,
   ObjectType           int                  null,
   Title                nvarchar(200)        null,
   Path                 nvarchar(200)        null,
   FileName             nvarchar(200)        null,
   FIleType             nvarchar(100)        null,
   constraint PK_ATTACHMENT primary key (ID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY]
go

/*==============================================================*/
/* Table: CarBrand                                              */
/*==============================================================*/
create table dbo.CarBrand (
   BrandID              int                  identity(1,1),
   BrandName            nvarchar(20)         null,
   Brand                nvarchar(20)         null,
   Flag                 nvarchar(50)         null,
   Notes                ntext                null,
   constraint PK_CARBRAND primary key (BrandID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go

/*==============================================================*/
/* Table: CarModel                                              */
/*==============================================================*/
create table dbo.CarModel (
   ModelID              int                  identity(1,1),
   ModelName            nvarchar(200)        null,
   ModelEnName          nvarchar(500)        null,
   constraint PK_CARMODEL primary key (ModelID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY]
go

/*==============================================================*/
/* Table: Category                                              */
/*==============================================================*/
create table dbo.Category (
   CategoryID           int                  identity(1,1),
   CategoryName         nvarchar(50)         null,
   ParentID             int                  null,
   Category             nvarchar(50)         null,
   Notes                ntext                null,
   constraint PK_CATEGORY primary key (CategoryID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go

/*==============================================================*/
/* Table: Color                                                 */
/*==============================================================*/
create table dbo.Color (
   ID                   bigint               identity(1,1),
   ColorName            nvarchar(200)        null,
   ColorEnName          nvarchar(200)        null,
   constraint PK_Color primary key (ID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY]
go

/*==============================================================*/
/* Table: Inventory                                             */
/*==============================================================*/
create table dbo.Inventory (
   ID                   bigint               identity(1,1),
   ProductID            bigint               null,
   Quantity             bigint               null,
   Type                 int                  null,
   Created              datetime             null,
   UserID               bigint               null,
   constraint PK_INVENTORY primary key (ID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY]
go

/*==============================================================*/
/* Table: Modules                                               */
/*==============================================================*/
create table Modules (
   ModuleID             int                  identity,
   ParentID             int                  null,
   ModuleName           nvarchar(200)        null,
   Route                nvarchar(100)        null,
   constraint PK_MODULES primary key (ModuleID)
)
go

/*==============================================================*/
/* Table: News                                                  */
/*==============================================================*/
create table dbo.News (
   NewsID               int                  identity(1,1),
   NewsCategory         int                  null,
   Title                nvarchar(200)        null,
   Content              ntext                null,
   Created              datetime             null,
   Display              int                  null,
   constraint PK_NEWS primary key (NewsID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go

/*==============================================================*/
/* Table: NewsCategory                                          */
/*==============================================================*/
create table dbo.NewsCategory (
   CategoryID           int                  identity(1,1),
   CategoryName         nvarchar(50)         null,
   ParentID             int                  null,
   Category             nvarchar(200)        null,
   constraint PK_NEWSCATEGORY primary key (CategoryID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY]
go

/*==============================================================*/
/* Table: OrderProducts                                         */
/*==============================================================*/
create table dbo.OrderProducts (
   ID                   int                  identity(1,1),
   OrderID              int                  null,
   ProductID            int                  null,
   Quantity             int                  null,
   constraint PK_ORDERPRODUCTS primary key (ID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY]
go

/*==============================================================*/
/* Table: PartBrand                                             */
/*==============================================================*/
create table dbo.PartBrand (
   ID                   int                  identity(1,1),
   BrandName            nvarchar(200)        null,
   BrandEnName          nvarchar(500)        null,
   Notes                ntext                null,
   constraint PK_PARTBRAND primary key (ID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go

/*==============================================================*/
/* Table: ProductCarModelRel                                    */
/*==============================================================*/
create table dbo.ProductCarModelRel (
   ID                   bigint               identity(1,1),
   ProductID            bigint               null,
   CarModelID           bigint               null,
   constraint PK_ProductCarModelRel primary key (ID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY]
go

/*==============================================================*/
/* Table: ProductColorRel                                       */
/*==============================================================*/
create table dbo.ProductColorRel (
   ID                   bigint               identity(1,1),
   ProductID            bigint               null,
   ColorID              bigint               null,
   constraint PK_ProductColorRel primary key (ID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY]
go

/*==============================================================*/
/* Table: ProductFavorablePrice                                 */
/*==============================================================*/
create table dbo.ProductFavorablePrice (
   ID                   bigint               identity(1,1),
   ProductID            bigint               null,
   ProductPriceID       bigint               null,
   MinNumber            int                  null,
   MaxNumber            int                  null,
   ProductPrice         decimal(10, 2)       null,
   Created              datetime             null,
   constraint PK_ProductFavorablePrice primary key (ID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY]
go

/*==============================================================*/
/* Table: ProductPrice                                          */
/*==============================================================*/
create table dbo.ProductPrice (
   ID                   bigint               identity(1,1),
   ProductID            bigint               null,
   ProductPrice         decimal(10, 2)       null,
   Created              datetime             null,
   constraint PK_ProductPrice primary key (ID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY]
go

/*==============================================================*/
/* Table: Products                                              */
/*==============================================================*/
create table dbo.Products (
   ProductID            bigint               identity(1,1),
   Category             int                  null,
   UserID               bigint               null,
   Brand                int                  null,
   ProductName          nvarchar(200)        null,
   ProductPrice         decimal(10, 2)       null,
   ModelType            nvarchar(200)        null,
   Specification        nvarchar(200)        null,
   Product              nvarchar(500)        null,
   Notes                ntext                null,
   Quantity             bigint               null,
   State                int                  null,
   constraint PK_PRODUCTS primary key (ProductID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go

/*==============================================================*/
/* Table: Purchasers                                            */
/*==============================================================*/
create table Purchasers (
   PurchaserNo          nvarchar(30)         not null,
   UserID               bigint               null,
   Type                 int                  null,
   ShortName            nvarchar(100)        null,
   Company              nvarchar(200)        null,
   Mobile               nvarchar(15)         null,
   Tel                  nvarchar(15)         null,
   Fax                  nvarchar(15)         null,
   Email                nvarchar(30)         null,
   Contact              nvarchar(20)         null,
   ZipCode              nvarchar(10)         null,
   Place                nvarchar(50)         null,
   Address              nvarchar(200)        null,
   Notes                ntext                null,
   Licence              nvarchar(200)        null,
   IDCard               nvarcahr(200)        null,
   Scope                nvarchar(400)        null,
   Sales                bigint               null,
   constraint PK_PURCHASERS primary key (PurchaserNo)
)
go

/*==============================================================*/
/* Table: Roles                                                 */
/*==============================================================*/
create table Roles (
   RoleID               int                  identity,
   RoleName             nvarchar(200)        null,
   RolePermission       nvarchar(800)        null,
   constraint PK_ROLES primary key (RoleID)
)
go

/*==============================================================*/
/* Table: SellOrder                                             */
/*==============================================================*/
create table dbo.SellOrder (
   OrderID              bigint               identity(1,1),
   UserID               bigint               null,
   RequireTime          datetime             null,
   Created              datetime             null,
   Contact              nvarchar(50)         null,
   Tel                  nvarchar(50)         null,
   Notes                ntext                null,
   Status               int                  null,
   constraint PK_SELLORDER primary key (OrderID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go

/*==============================================================*/
/* Table: ShipmentAddress                                       */
/*==============================================================*/
create table dbo.ShipmentAddress (
   ShipmentID           bigint               identity(1,1),
   UserID               bigint               null,
   Contact              nvarchar(20)         null,
   Address              nvarchar(200)        null,
   ZipCode              nvarchar(10)         null,
   PhoneNumber          nvarchar(20)         null,
   Notes                ntext                null,
   constraint PK_SHIPMENTADDRESS primary key (ShipmentID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go

/*==============================================================*/
/* Table: Suppliers                                             */
/*==============================================================*/
create table Suppliers (
   SupplierNo           nvarchar(30)         not null,
   UserID               bigint               null,
   ShortName            nvarchar(100)        null,
   CompanyName          nvarchar(200)        null,
   Mobile               nvarchar(15)         null,
   Tel                  nvarchar(15)         null,
   Fax                  nvarchar(15)         null,
   Email                nvarchar(30)         null,
   Contact              nvarchar(20)         null,
   ZipCode              nvarchar(10)         null,
   Place                nvarchar(50)         null,
   Address              nvarchar(200)        null,
   Description          ntext                null,
   Notes                ntext                null,
   Licence              nvarchar(200)        null,
   IdCard               nvarchar(200)        null,
   Authorize            nvarchar(200)        null,
   Cert                 nvarhcar(200)        null,
   constraint PK_SUPPLIERS primary key (SupplierNo)
)
go

/*==============================================================*/
/* Table: Users                                                 */
/*==============================================================*/
create table dbo.Users (
   UserID               bigint               identity(1,1),
   Username             nvarchar(50)         null,
   Password             nvarchar(50)         null,
   UserType             int                  null,
   ParentID             bigint               null,
   Name                 nvarchar(50)         null,
   Role                 int                  null,
   Tel                  nvarchar(30)         null,
   BirthDay             datetime             null,
   Email                nvarchar(30)         null,
   Address              nvarchar(200)        null,
   Notes                ntext                null,
   constraint PK_USERS primary key (UserID)
         WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go

